import sys
from pathlib import Path

file = Path(__file__).resolve()
parent, top = file.parent, file.parents[2]

sys.path.append(str(top))
try:
    sys.path.remove(str(parent))
except ValueError: # Already removed
    pass

__package__ = 'src.model'

from ..utils.ids import create_id, datetime_8, get_ip_address
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy import Column, String, BigInteger, CHAR, DateTime, Integer
from ..utils.database import Base, SessionLocal, engine
# from datetime import datetime, timedelta
from sqlalchemy.sql import text
import json


class Failure(Base):
    __tablename__ = 'tb_failure'
    __table_args__ = {'comment': '缺陷表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    failure_type = Column(String(32), nullable=False, comment='缺陷类型')
    failure_name = Column(String(32), nullable=False, comment='缺陷名称')
    failure_id = Column(BigInteger, nullable=False, comment='缺陷 id 做关联查询')
    del_flag = Column(
        CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")


class Lot(Base):
    __tablename__ = 'tb_lot'
    __table_args__ = {'comment': 'lot 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    lot_code = Column(String(32), nullable=False, default=create_id, comment='lot code')
    lot_name = Column(String(32), nullable=False, comment='lot名称')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    material_code = Column(LONGTEXT, nullable=False, comment='料号code')
    aoi_code = Column(LONGTEXT, comment='料号code')
    dir_path = Column(String(255), nullable=False, server_default='', comment='lot文件夹目录路径')
    status = Column(String(16), nullable=False, server_default='NO_COMPLETED', comment='状态: NO_COMPLETED-未完成, COMPLETED-已完成')
    comment = Column(String(50), comment='lot 备注')
    start_time = Column(DateTime, default=datetime_8, comment='开始时间')
    end_time = Column(DateTime, comment='结束时间')
    del_flag = Column(CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")


# 每个 panel 上的错误点图片
class FailureImage(Base):
    __tablename__ = 'tb_failure_image'
    __table_args__ = {'comment': 'image 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    image_code = Column(String(32), nullable=False, default=create_id, comment='image code')
    lot_code = Column(
        String(255), nullable=False, comment='lot code 做关联')
    panel_code = Column(
        String(255), nullable=False, comment='pannel image code 做关联')
    file_path = Column(String(255), nullable=False, comment='图片路径')
    file_name = Column(String(255), nullable=False, comment='图片名')
    panel_id = Column(String(32), nullable=False, comment='Panel ID')
    failure_id = Column(
        BigInteger, nullable=False, comment='缺陷 id 做关联查询')
    del_flag = Column(CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")
    aoi_failure = Column(String(16), nullable=False, comment='aoi 缺陷')
    pattern_failure = Column(String(16), nullable=False, comment='pattern 缺陷')


# 每个 panel 对应的图片
class PanelImage(Base):
    __tablename__ = 'tb_panel_image'
    __table_args__ = {'comment': 'image 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    panel_code = Column(String(32), nullable=False, default=create_id, comment='panel code')
    panel_id = Column(String(32), nullable=False, comment='Panel ID')
    lot_code = Column(String(255), nullable=False, comment='lot code 做关联')
    aoi_code = Column(String(255), comment='aoi code 做关联')
    material_code = Column(String(255), nullable=False, comment='materia code 做关联')
    file_path = Column(String(255), nullable=False, comment='图片路径')
    thumb_file_path = Column(String(255), nullable=False, comment='图片的缩略图路径')
    aoi_file_path = Column(String(255), nullable=False, comment='aoi 图片路径')
    detection_info = Column(LONGTEXT, comment='去重之后的检测信息')
    all_detection_info = Column(LONGTEXT, comment='所有检测信息')
    segmentation_info = Column(LONGTEXT, default="", comment='分割信息')
    ai_result = Column(Integer, comment='AI 检测结果, 1-OK, 2-NG, 3-Gray')
    aoi_result = Column(Integer, comment='AOI 检测结果, 1-OK, 2-NG')
    judgment = Column(Integer, default=0, comment='人工复判结果, 0-未复判, 1-OK, 2-NG')
    start_time = Column(DateTime, default=datetime_8, comment='Lot 开始时间')
    end_time = Column(DateTime, default=datetime_8, comment='Lot 结束时间')
    del_flag = Column(CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")
    aoi_failures = Column(LONGTEXT, default="", comment='过滤的 aoi 缺陷信息')
    failure_code = Column(String(32), comment='人工输入的缺陷代码')
    user_id = Column(String(32), comment='人工复判的用户 ID')
    img_roi = Column(String(255), comment='图片的有效区域')
    aoi_time = Column(DateTime, comment='AOI 时间')



# 每个 panel 对应的图片
class Material(Base):
    __tablename__ = 'tb_material'
    __table_args__ = {'comment': 'material 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}
    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    material_code = Column(String(32), nullable=False, default=create_id, comment='material code')
    material_name = Column(String(32), nullable=False, comment='料号')
    background_image = Column(String(255), nullable=False, comment='物料背景图')
    create_time = Column(DateTime, default=datetime_8, comment='物料创建时间')
    update_time = Column(DateTime, default=datetime_8, comment='物料更新时间')
    comment = Column(String(50), comment='物料备注')
    parameter = Column(LONGTEXT, comment='缺陷参数')
    del_flag = Column(CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")
    failure_map = Column(LONGTEXT, comment='画面模式下缺陷名称转换')


class AOI(Base):
    __tablename__ = 'tb_aoi'
    __table_args__ = {'comment': '远程拷贝 aoi 服务器表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}
    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    aoi_code = Column(String(32), nullable=False, default=create_id, comment='aoi code')
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    # 192.168.23.12
    hostname = Column(String(20), comment='AOI 服务器 ip 地址')
    port = Column(String(10), server_default='22',comment='AOI 服务器 ssh 端口')
    # administrator
    username = Column(String(20), comment='AOI 服务器用户名')
    # 123456
    password = Column(String(20), comment='AOI 服务器密码')
    lotdir = Column(String(255), comment='lot 目录')
    logdir = Column(String(255), comment='日志目录')
    aoi_name = Column(String(32), nullable=False, comment='aoi 名称')
    auto_status = Column(CHAR(1), server_default='0',comment='自动开始拷贝任务状态, 0-停止, 1-开始')
    auot_starttime = Column(DateTime, comment='自动开始拷贝开始时间')


if __name__=="__main__":
    # chip_id="GR5V2297SDD"
    # lot_codes=['20250418-090646-653c']
    # with SessionLocal() as session:
    #     # panel_image = session.query(PanelImage).filter(PanelImage.panel_id==chip_id).filter(PanelImage.lot_code.in_(lot_codes)).order_by(PanelImage.start_time.desc()).first()

    #     # print(panel_image)

    #     tname = "tb_panel_image"
    #     attr_name = "user_id"
    #     column_type = "varchar(32)"
    #     comment = "人工复判的用户 ID"
    #     alter_sql = f"ALTER TABLE {tname} ADD COLUMN {attr_name} {column_type} COMMENT '{comment}'"

    #     # 添加列
    #     session.execute(text(alter_sql))
    # 创建表
    Base.metadata.create_all(bind=engine)
